package dao;

import dto.CoberturaDTO;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author lucifer
 */
public class CoberturaDAO {

  public static final String Tab =
    "create table cobertura (" +
    "id serial," + //pk
    "id_medico int," + //fk
    "id_obra_social int," + //fk
    "id_plan_obra_social int" + //fk
    ");" +

    "alter table cobertura add constraint cobertura_obra_social_fk foreign key (id_obra_social) references obra_social(id);" +
    "alter table cobertura add constraint cobertura_plan_obra_social_fk foreign key (id_plan_obra_social) references obra_social_plan(id);"
  ;

  public static final String Tuples = "";
    /*
    "insert into cobertura (id_medico,id_obra_social,id_plan_obra_social) values" +
    // la 0, 0 siempre vá
    "(1, 0, 0)," +
    "(2, 0, 0)," +
    "(3, 0, 0)," +
    "(4, 0, 0)," +
    "(5, 0, 0)," +
    "(6, 0, 0)," +
    "(7, 0, 0)," +
    "(8, 0, 0)," +
    "(9, 0, 0)," +
    "(10, 0, 0)," +
    "(11, 0, 0)," +
    "(12, 0, 0)," +

    "(1, 1, 1)," +
    "(1, 1, 2)," +
    "(1, 1, 3)," +
    "(1, 2, 4)," +
    "(1, 2, 5)," +
    "(1, 3, 6)," +
    "(1, 3, 7)," +
    "(1, 3, 8)," +
    "(1, 3, 9)," +
    "(1, 4, 10)," +
    "(1, 4, 11)," +
    "(1, 4, 12)," +
    "(1, 4, 13)," +
    "(1, 4, 14)," +
    "(1, 4, 15)," +
    "(1, 4, 16)," +
    "(1, 4, 17)," +
    "(1, 4, 18)," +
    "(1, 4, 19)," +
    "(1, 5, 20)," +
    "(1, 6, 21)," +
    "(1, 6, 22)," +
    "(1, 6, 23)," +
    "(1, 7, 24)," +
    "(1, 7, 25)," +

    "(2, 1, 1)," +
    "(2, 1, 2)," +
    "(2, 2, 4)," +
    "(2, 2, 5)," +
    "(2, 3, 6)," +
    "(2, 3, 7)," +
    "(2, 4, 10)," +
    "(2, 4, 11)," +
    "(2, 5, 20)," +
    "(2, 6, 21)," +
    "(2, 6, 22)," +
    "(2, 7, 24)," +
    "(2, 7, 25)," +

    "(3, 1, 1)," +
    "(3, 2, 4)," +
    "(3, 3, 6)," +
    "(3, 4, 10)," +
    "(3, 5, 20)," +
    "(3, 6, 21)," +
    "(3, 7, 24)," +

    "(4, 1, 2)," +
    "(4, 2, 5)," +
    "(4, 3, 7)," +
    "(4, 4, 11)," +
    "(4, 6, 22)," +
    "(4, 7, 25)," +

    "(5, 3, 9)," +
    "(5, 4, 13)," +
    "(5, 4, 14)," +

    "(6, 1, 2)," +
    "(6, 2, 5)," +
    "(6, 3, 7)," +
    "(6, 3, 9)," +
    "(6, 4, 11)," +
    "(6, 4, 13)," +
    "(6, 4, 15)," +
    "(6, 4, 17)," +
    "(6, 4, 19)," +
    "(6, 6, 22)," +
    "(6, 7, 25)," +

    "(7, 1, 1)," +
    "(7, 1, 3)," +
    "(7, 2, 4)," +
    "(7, 3, 6)," +
    "(7, 3, 8)," +
    "(7, 4, 10)," +
    "(7, 4, 12)," +
    "(7, 4, 14)," +
    "(7, 4, 16)," +
    "(7, 4, 18)," +
    "(7, 5, 20)," +
    "(7, 6, 21)," +
    "(7, 6, 23)," +
    "(7, 7, 24)," +

    "(9, 1, 3)," +
    "(9, 2, 5)," +
    "(9, 3, 9)," +
    "(9, 4, 19)," +
    "(9, 5, 20)," +
    "(9, 6, 23)," +
    "(9, 7, 25)," +

    "(10, 2, 4)," +
    "(10, 2, 5)," +
    "(10, 4, 10)," +
    "(10, 4, 11)," +
    "(10, 4, 12)," +
    "(10, 4, 13)," +
    "(10, 4, 14)," +
    "(10, 4, 15)," +
    "(10, 4, 16)," +
    "(10, 4, 17)," +
    "(10, 4, 18)," +
    "(10, 4, 19)," +
    "(10, 6, 21)," +
    "(10, 6, 22)," +
    "(10, 6, 23)," +

    "(11, 1, 1)," +
    "(11, 1, 2)," +
    "(11, 1, 3)," +
    "(11, 3, 6)," +
    "(11, 3, 7)," +
    "(11, 3, 8)," +
    "(11, 3, 9)," +
    "(11, 5, 20)," +
    "(11, 7, 24)," +
    "(11, 7, 25)," +

    "(12, 1, 1)," +
    "(12, 1, 3)," +
    "(12, 2, 5)," +
    "(12, 3, 7)," +
    "(12, 3, 9)," +
    "(12, 4, 11)," +
    "(12, 4, 13)," +
    "(12, 4, 15)," +
    "(12, 4, 17)," +
    "(12, 4, 19)," +
    "(12, 6, 21)," +
    "(12, 6, 23)," +
    "(12, 7, 25);"
  ;
  */

  private static CoberturaDAO dao = null;
  private final Connection c;

  public static CoberturaDAO createDAO() throws SQLException  {
    if (dao == null) {
      dao = new CoberturaDAO();
    }
    return dao;
  }

  private CoberturaDAO() throws SQLException {
    c = DB.PQ().Conn();
  }

  public int LastID() throws SQLException {
    return DB.PQ().LastIDFrom("cobertura");
  }

  public ArrayList<CoberturaDTO> SearchByMedicoID(int idMedico) throws SQLException {
    return getCoberturaDTOs(queryByMedicoID(idMedico));
  }

  private String queryByMedicoID(int idMedico) throws SQLException {
    String query = String.format(
      "select c.* from cobertura c, obra_social o, obra_social_plan p where id_medico = %d and c.id_obra_social = o.id and c.id_plan_obra_social = p.id order by o.nombre, p.nombre;", idMedico
    );
    return query;
  }

  private ArrayList<CoberturaDTO> getCoberturaDTOs(String query) throws SQLException {
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(query);
    ArrayList<CoberturaDTO> pacientes = new ArrayList<>();
    while (r.next()) {
      pacientes.add(
        new CoberturaDTO(
          r.getInt("id"),
          r.getInt("id_medico"),
          r.getInt("id_obra_social"),
          r.getInt("id_plan_obra_social")
        )
      );
    }
    r.close();
    s.close();
    return pacientes;
  }

  public CoberturaDTO CoberturaDTO(int id) throws SQLException {
    Statement s = c.createStatement();
    ResultSet r = s.executeQuery(
      "select * from cobertura where id = " + id
    );
    CoberturaDTO dto = null;
    if (r.next()) {
      dto = new CoberturaDTO(
        r.getInt("id"),
        r.getInt("id_medico"),
        r.getInt("id_obra_social"),
        r.getInt("id_plan_obra_social")
      );
    }
    r.close();
    s.close();
    return dto; //si no existe devuelve null
  }

  public int Save(CoberturaDTO cob) throws SQLException {
    String query = String.format(
      "insert into cobertura values (default, %d, %d, %d);",
      cob.getIdMedico(), cob.getIdObraSocial(), cob.getIdPlanObraSocial()
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
    return LastID();
  }

  public void DeleteByMedicoID(int id) throws SQLException {
    Statement s = c.createStatement();
    s.executeUpdate("delete from cobertura where id_medico = " + id);
    s.close();
  }

  public CoberturaDTO EditInt(int id, String attr, int n) throws SQLException {
    String query = String.format(
      "update cobertura set %s = %d where id = %d;",
      attr, n, id
    );
    Statement s = c.createStatement();
    s.executeUpdate(query);
    s.close();
    return CoberturaDTO(id);
  }

}
